import pandas as pd
import pymysql

from flask import Flask, render_template, request

app = Flask(__name__)

db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
cursor = db.cursor()


# sql = 'select * from side_table where type = "点位"'

def select_all_mysql(sql):
    db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        # 只能有一个cursor.fetchall()
        result = cursor.fetchall()
        return result
    except:
        import traceback
        traceback.print_exc()
        print("Error: unable to fetch data")
    db.close()


@app.route('/')
def index():
    return 'Hello World'



@app.route('/look', methods=['POST', 'GET'])
def look():
    if request.method == "POST":
        project = request.form.get('project')
        import collections
        a = []
        all = []
        all_pd = {}
        ll_pd = {}
        alll_pd = {}
        zs = 0
        sql = 'select name from main_table where project="%s" and quantitative > "0.0"'%(project)
        db = pymysql.connect(host="localhost", user="root", password="123456", database="project_01")
        cursor = db.cursor()
        try:
            cursor.execute(sql)
            result = cursor.fetchall()
            print(result)
            for i in result:
                print(i[0])
                all.append(i[0])
                if i[0] in a:
                    pass
                else:
                    a.append(i[0])
            print(a)
            print(all)
            print(collections.Counter(all))
            max_all = collections.Counter(all)
            print('-----')
            for i in max_all:
                print(i)
                print(max_all[i])
                zs += max_all[i]
                all_pd[i] = max_all[i]

            for i in max_all:
                yg = (max_all[i]/zs)*100
                yg = '{:g}'.format (yg)
                print(yg)
                ll_pd[i] = yg

            print(all_pd)
            alll_pd['次数'] = all_pd
            alll_pd['频数'] = ll_pd
            print(alll_pd)
            print(zs)

            print('successful')
        except:
            import traceback

            traceback.print_exc()
            print("Error: unable to fetch data")
        db.close()

        return alll_pd
    else:
        print('sb')

    return render_template('1.html')
